//go:build !wailsbindings
// +build !wailsbindings

package services

import (
	"context"
	"encoding/csv"
	"encoding/json"
	"fmt"
	"os"
	"path/filepath"
	"sort"
	"strconv"
	"strings"
	"time"

	"github.com/pkg/errors"
	"github.com/sirupsen/logrus"
	"github.com/xuri/excelize/v2"

	"changeme/internal/config"
	"changeme/internal/database"
	"changeme/internal/models"
	"changeme/internal/utils"
)

// DatabaseService 数据库服务
type DatabaseService struct {
	manager database.DatabaseManager
	logger  *logrus.Logger
	ctx     context.Context
}

// NewDatabaseService 创建数据库服务实例
func NewDatabaseService() (*DatabaseService, error) {
	logger := logrus.New()
	logger.SetLevel(logrus.DebugLevel) // 设置为DEBUG级别以便调试

	// 获取配置目录
	configDir, err := getConfigDir()
	if err != nil {
		return nil, errors.Wrap(err, "failed to get config directory")
	}

	// 创建配置存储
	storage, err := config.NewGormStorage(filepath.Join(configDir, "database_configs.db"))
	if err != nil {
		return nil, errors.Wrap(err, "failed to create config storage")
	}

	// 创建密码管理器
	passwordManager := utils.NewSimplePasswordManager() // 在生产环境中应该使用AES加密

	// 创建驱动工厂
	factory := database.NewFactory()

	// 创建数据库管理器
	manager := database.NewManager(storage, factory, nil, passwordManager)

	return &DatabaseService{
		manager: manager,
		logger:  logger,
		ctx:     context.Background(),
	}, nil
}

// 配置管理方法

// SaveConfig 保存数据库配置
func (s *DatabaseService) SaveConfig(config *models.DatabaseConfig) (*models.DatabaseConfig, error) {
	s.logger.WithFields(logrus.Fields{
		"name": config.Name,
		"type": config.Type,
		"host": config.Host,
	}).Info("Saving database config")

	if err := s.manager.SaveConfig(config); err != nil {
		return nil, err
	}

	// 返回保存后的配置（包含生成的ID）
	return config, nil
}

// GetConfig 获取数据库配置
func (s *DatabaseService) GetConfig(id string) (*models.DatabaseConfig, error) {
	return s.manager.GetConfig(id)
}

// GetAllConfigs 获取所有数据库配置
func (s *DatabaseService) GetAllConfigs() ([]*models.DatabaseConfig, error) {
	return s.manager.GetAllConfigs()
}

// DeleteConfig 删除数据库配置
func (s *DatabaseService) DeleteConfig(id string) error {
	s.logger.WithField("config_id", id).Info("Deleting database config")

	// 添加超时控制
	ctx, cancel := context.WithTimeout(context.Background(), 10*time.Second)
	defer cancel()

	// 创建一个通道接收结果
	resultCh := make(chan error, 1)

	// 在单独的goroutine中执行删除操作
	go func() {
		err := s.manager.DeleteConfig(id)
		select {
		case resultCh <- err:
			// 成功发送结果
		default:
			// 通道已关闭或满，忽略
		}
	}()

	// 等待结果或超时
	select {
	case err := <-resultCh:
		if err != nil {
			s.logger.WithError(err).WithField("config_id", id).Error("Failed to delete database config")
			return err
		}
		s.logger.WithField("config_id", id).Info("Database config deleted successfully")
		return nil
	case <-ctx.Done():
		s.logger.WithField("config_id", id).Error("Delete database config timeout")
		return errors.New("删除连接超时，请稍后再试")
	}
}

// UpdateConfig 更新数据库配置
func (s *DatabaseService) UpdateConfig(config *models.DatabaseConfig) (*models.DatabaseConfig, error) {
	s.logger.WithFields(logrus.Fields{
		"config_id": config.ID,
		"name":      config.Name,
	}).Info("Updating database config")

	if err := s.manager.UpdateConfig(config); err != nil {
		return nil, err
	}

	// 返回更新后的配置
	return config, nil
}

// SetDefaultConfig 设置默认数据库配置
func (s *DatabaseService) SetDefaultConfig(id string) error {
	return s.manager.SetDefaultConfig(id)
}

// GetDefaultConfig 获取默认数据库配置
func (s *DatabaseService) GetDefaultConfig() (*models.DatabaseConfig, error) {
	return s.manager.GetDefaultConfig()
}

// 连接管理方法

// TestConnection 测试数据库连接
func (s *DatabaseService) TestConnection(config *models.DatabaseConfig) error {
	s.logger.WithFields(logrus.Fields{
		"name": config.Name,
		"type": config.Type,
		"host": config.Host,
	}).Info("Testing database connection")

	return s.manager.TestConnection(config)
}

// GetConnectionStatus 获取连接状态
func (s *DatabaseService) GetConnectionStatus(configID string) (*models.ConnectionStatus, error) {
	return s.manager.GetConnectionStatus(configID)
}

// CloseConnection 关闭数据库连接
func (s *DatabaseService) CloseConnection(configID string) error {
	s.logger.WithField("config_id", configID).Info("Closing database connection")
	return s.manager.CloseConnection(configID)
}

// CloseAllConnections 关闭所有数据库连接
func (s *DatabaseService) CloseAllConnections() error {
	s.logger.Info("Closing all database connections")
	return s.manager.CloseAllConnections()
}

// 数据库操作方法

// ListDatabases 列出数据库
func (s *DatabaseService) ListDatabases(configID string) ([]models.DatabaseInfo, error) {
	s.logger.WithField("config_id", configID).Debug("Listing databases")
	return s.manager.ListDatabases(configID)
}

// ListTables 列出表
func (s *DatabaseService) ListTables(configID, database string) ([]models.TableInfo, error) {
	s.logger.WithFields(logrus.Fields{
		"config_id": configID,
		"database":  database,
	}).Debug("Listing tables")

	return s.manager.ListTables(configID, database)
}

// GetTableColumns 获取表列信息
func (s *DatabaseService) GetTableColumns(configID, database, table string) ([]models.ColumnInfo, error) {
	s.logger.WithFields(logrus.Fields{
		"config_id": configID,
		"database":  database,
		"table":     table,
	}).Debug("Getting table columns")

	// 参数验证
	if configID == "" {
		return nil, errors.New("connection ID is required")
	}
	if database == "" {
		return nil, errors.New("database name is required")
	}
	if table == "" {
		return nil, errors.New("table name is required")
	}

	// 获取列信息
	columns, err := s.manager.GetTableColumns(configID, database, table)
	if err != nil {
		s.logger.WithError(err).WithFields(logrus.Fields{
			"config_id": configID,
			"database":  database,
			"table":     table,
		}).Error("Failed to get table columns")
		return nil, errors.Wrap(err, "failed to get table columns")
	}

	// 如果没有列信息，记录日志
	if len(columns) == 0 {
		s.logger.WithFields(logrus.Fields{
			"config_id": configID,
			"database":  database,
			"table":     table,
		}).Warn("No columns found for table")
	}

	// 获取数据库类型（用于前端渲染）
	config, err := s.GetConfig(configID)
	if err != nil {
		// 配置错误不影响列信息返回，只记录日志
		s.logger.WithError(err).WithField("config_id", configID).
			Warn("Failed to get database type from config")
		return columns, nil
	}

	// 添加数据库类型到日志
	s.logger.WithFields(logrus.Fields{
		"config_id":    configID,
		"database":     database,
		"table":        table,
		"column_count": len(columns),
		"db_type":      config.Type,
	}).Info("Successfully retrieved table columns")

	return columns, nil
}

// GetTableIndexes 获取表索引信息
func (s *DatabaseService) GetTableIndexes(configID, database, table string) ([]models.IndexInfo, error) {
	s.logger.WithFields(logrus.Fields{
		"config_id": configID,
		"database":  database,
		"table":     table,
	}).Debug("Getting table indexes")

	return s.manager.GetTableIndexes(configID, database, table)
}

// ExecuteQuery 执行查询
func (s *DatabaseService) ExecuteQuery(configID, query string, args ...interface{}) (*models.QueryResult, error) {
	s.logger.WithFields(logrus.Fields{
		"config_id": configID,
		"query":     query[:min(len(query), 100)] + "...", // 只记录前100个字符
	}).Info("Executing query")

	return s.manager.ExecuteQuery(configID, query, args...)
}

// ExecuteNonQuery 执行非查询语句
func (s *DatabaseService) ExecuteNonQuery(configID, query string, args ...interface{}) (int64, error) {
	s.logger.WithFields(logrus.Fields{
		"config_id": configID,
		"query":     query[:min(len(query), 100)] + "...",
	}).Info("Executing non-query")

	return s.manager.ExecuteNonQuery(configID, query, args...)
}

// ExecuteBatchSQL 批量执行SQL语句
// 支持执行多条SQL语句，返回每条语句的执行结果
func (s *DatabaseService) ExecuteBatchSQL(configID string, sqlStatements []string) (*models.BatchExecuteResult, error) {
	s.logger.WithFields(logrus.Fields{
		"config_id": configID,
		"count":     len(sqlStatements),
	}).Info("Executing batch SQL")

	// 参数验证
	if configID == "" {
		return nil, errors.New("connection ID is required")
	}
	if len(sqlStatements) == 0 {
		return nil, errors.New("no SQL statements provided")
	}

	// 获取数据库连接和配置
	config, err := s.GetConfig(configID)
	if err != nil {
		return nil, errors.Wrap(err, "failed to get database config")
	}

	driver, err := s.manager.GetConnection(configID)
	if err != nil {
		return nil, errors.Wrap(err, "failed to get database connection")
	}

	// 创建批量执行结果
	result := &models.BatchExecuteResult{
		TotalStatements: len(sqlStatements),
		Results:         make([]models.StatementResult, 0, len(sqlStatements)),
		StartTime:       time.Now(),
	}

	// 判断是否需要使用事务
	// 对于某些数据库（如达梦），DDL语句不能在事务中执行
	useTransaction := shouldUseTransaction(config.Type, sqlStatements)

	var tx database.DatabaseTransaction
	if useTransaction {
		// 开启事务
		tx, err = driver.BeginTransaction(s.ctx)
		if err != nil {
			s.logger.WithError(err).Warn("Failed to begin transaction, will execute without transaction")
			useTransaction = false
		}
	}

	// 执行每条SQL语句
	for i, sql := range sqlStatements {
		sql = strings.TrimSpace(sql)
		if sql == "" {
			continue
		}

		stmtResult := models.StatementResult{
			Index:     i,
			SQL:       sql,
			StartTime: time.Now(),
		}

		// 判断SQL类型
		isQuery := isQueryStatement(sql)

		// 执行SQL
		if useTransaction && tx != nil {
			// 在事务中执行
			if isQuery {
				queryResult, err := tx.ExecuteQuery(s.ctx, sql)
				if err != nil {
					stmtResult.Error = err.Error()
					stmtResult.Success = false
				} else {
					stmtResult.Success = true
					stmtResult.RowCount = queryResult.RowCount
					stmtResult.QueryResult = queryResult
				}
			} else {
				affected, err := tx.ExecuteNonQuery(s.ctx, sql)
				if err != nil {
					stmtResult.Error = err.Error()
					stmtResult.Success = false
				} else {
					stmtResult.Success = true
					stmtResult.AffectedRows = affected
				}
			}
		} else {
			// 不使用事务，直接执行
			if isQuery {
				queryResult, err := s.manager.ExecuteQuery(configID, sql)
				if err != nil {
					stmtResult.Error = err.Error()
					stmtResult.Success = false
				} else {
					stmtResult.Success = true
					stmtResult.RowCount = queryResult.RowCount
					stmtResult.QueryResult = queryResult
				}
			} else {
				affected, err := s.manager.ExecuteNonQuery(configID, sql)
				if err != nil {
					stmtResult.Error = err.Error()
					stmtResult.Success = false
				} else {
					stmtResult.Success = true
					stmtResult.AffectedRows = affected
				}
			}
		}

		// 计算执行时间
		stmtResult.ExecuteTime = time.Since(stmtResult.StartTime)
		result.Results = append(result.Results, stmtResult)

		// 如果在事务中执行失败，根据数据库类型决定是否继续
		if useTransaction && !stmtResult.Success {
			if !shouldContinueOnError(config.Type) {
				result.StoppedOnError = true
				break
			}
		}
	}

	// 处理事务提交或回滚
	if useTransaction && tx != nil {
		// 检查是否有失败的语句
		hasError := false
		for _, r := range result.Results {
			if !r.Success {
				hasError = true
				break
			}
		}

		if hasError && !shouldContinueOnError(config.Type) {
			// 回滚事务
			if err := tx.Rollback(); err != nil {
				s.logger.WithError(err).Error("Failed to rollback transaction")
			}
			result.TransactionRolledBack = true
		} else {
			// 提交事务
			if err := tx.Commit(); err != nil {
				s.logger.WithError(err).Error("Failed to commit transaction")
				result.TransactionRolledBack = true
				// 标记所有成功的语句为失败
				for i := range result.Results {
					if result.Results[i].Success {
						result.Results[i].Success = false
						result.Results[i].Error = "Transaction commit failed: " + err.Error()
					}
				}
			} else {
				result.TransactionCommitted = true
			}
		}
	}

	// 计算总执行时间和成功数量
	result.TotalExecuteTime = time.Since(result.StartTime)
	for _, r := range result.Results {
		if r.Success {
			result.SuccessCount++
		} else {
			result.FailedCount++
		}
	}

	s.logger.WithFields(logrus.Fields{
		"config_id":       configID,
		"total":           result.TotalStatements,
		"success":         result.SuccessCount,
		"failed":          result.FailedCount,
		"execute_time":    result.TotalExecuteTime,
		"use_transaction": useTransaction,
	}).Info("Batch SQL execution completed")

	return result, nil
}

// shouldUseTransaction 判断是否应该使用事务
func shouldUseTransaction(dbType models.DatabaseType, statements []string) bool {
	// 达梦数据库的DDL语句不能在事务中执行
	if dbType == models.DaMeng {
		for _, sql := range statements {
			sqlUpper := strings.ToUpper(strings.TrimSpace(sql))
			// 检查是否包含DDL语句
			if strings.HasPrefix(sqlUpper, "CREATE ") ||
				strings.HasPrefix(sqlUpper, "ALTER ") ||
				strings.HasPrefix(sqlUpper, "DROP ") ||
				strings.HasPrefix(sqlUpper, "TRUNCATE ") ||
				strings.HasPrefix(sqlUpper, "COMMENT ") {
				return false
			}
		}
	}

	// 其他数据库默认使用事务
	return true
}

// shouldContinueOnError 判断出错时是否继续执行
func shouldContinueOnError(dbType models.DatabaseType) bool {
	// 达梦数据库在批量执行时，某些错误可以继续执行
	if dbType == models.DaMeng {
		return true
	}
	// 其他数据库默认停止执行
	return false
}

// isQueryStatement 判断是否为查询语句
func isQueryStatement(sql string) bool {
	sqlUpper := strings.ToUpper(strings.TrimSpace(sql))
	return strings.HasPrefix(sqlUpper, "SELECT ") ||
		strings.HasPrefix(sqlUpper, "WITH ") ||
		strings.HasPrefix(sqlUpper, "SHOW ") ||
		strings.HasPrefix(sqlUpper, "DESC ") ||
		strings.HasPrefix(sqlUpper, "DESCRIBE ") ||
		strings.HasPrefix(sqlUpper, "EXPLAIN ")
}

// 工具方法

// GetSupportedDatabaseTypes 获取支持的数据库类型
func (s *DatabaseService) GetSupportedDatabaseTypes() []models.DatabaseType {
	factory := database.NewFactory()
	return factory.SupportedTypes()
}

// GetDatabaseTypeInfo 获取数据库类型信息
func (s *DatabaseService) GetDatabaseTypeInfo() map[models.DatabaseType]map[string]interface{} {
	factory := database.NewFactory()
	return factory.GetDriverInfo()
}

// ValidateConfig 验证数据库配置
func (s *DatabaseService) ValidateConfig(config *models.DatabaseConfig) error {
	if config.Name == "" {
		return errors.New("config name is required")
	}

	if config.Type == "" {
		return errors.New("database type is required")
	}

	// 根据数据库类型验证必需字段
	switch config.Type {
	case models.MySQL, models.PostgreSQL, models.SQLServer:
		if config.Host == "" {
			return errors.New("host is required")
		}
		if config.Port <= 0 {
			return errors.New("valid port is required")
		}
		if config.Username == "" {
			return errors.New("username is required")
		}
		// 注意：不再验证Database字段，因为我们连接到服务器而不是特定数据库
	case models.SQLite:
		if config.FilePath == "" {
			return errors.New("file path is required for SQLite")
		}
	default:
		return errors.New("unsupported database type")
	}

	return nil
}

// GetDefaultConfigForType 根据数据库类型获取默认配置
func (s *DatabaseService) GetDefaultConfigForType(dbType models.DatabaseType) *models.DatabaseConfig {
	config := &models.DatabaseConfig{
		Type:      dbType,
		Name:      fmt.Sprintf("New %s Connection", dbType),
		Charset:   "utf8mb4",
		IsDefault: false,
	}

	// 根据不同数据库类型设置默认值
	switch dbType {
	case models.MySQL:
		config.Host = "localhost"
		config.Port = 3306
		config.Username = "root"
	case models.PostgreSQL:
		config.Host = "localhost"
		config.Port = 5432
		config.Username = "postgres"
	case models.SQLServer:
		config.Host = "localhost"
		config.Port = 1433
		config.Username = "sa"
	case models.SQLite:
		// 为SQLite创建一个默认数据库文件
		configDir, err := getConfigDir()
		if err != nil {
			s.logger.WithError(err).Error("Failed to get config directory")
			config.FilePath = "./default.db"
		} else {
			config.FilePath = filepath.Join(configDir, "default.db")
		}
	case models.DaMeng:
		config.Host = "localhost"
		config.Port = 5236
		config.Username = "SYSDBA"
		config.Password = "SYSDBA"
		config.Charset = "UTF8"
	}

	return config
}

// 私有辅助方法

// getConfigDir 获取配置目录
func getConfigDir() (string, error) {
	homeDir, err := os.UserHomeDir()
	if err != nil {
		return "", err
	}

	configDir := filepath.Join(homeDir, ".wails-database-manager")
	if err := os.MkdirAll(configDir, 0755); err != nil {
		return "", err
	}

	return configDir, nil
}

// min 返回两个整数中的较小值
func min(a, b int) int {
	if a < b {
		return a
	}
	return b
}

// Shutdown 关闭服务
func (s *DatabaseService) Shutdown() error {
	s.logger.Info("Shutting down database service")
	return s.manager.CloseAllConnections()
}

// TestTableColumnsInfo 用于测试改进后的列信息实现
func (s *DatabaseService) TestTableColumnsInfo(configID, database, table string) (map[string]interface{}, error) {
	s.logger.WithFields(logrus.Fields{
		"config_id": configID,
		"database":  database,
		"table":     table,
	}).Info("Testing enhanced table columns implementation")

	// 获取列信息
	columns, err := s.GetTableColumns(configID, database, table)
	if err != nil {
		return nil, errors.Wrap(err, "failed to get table columns")
	}

	// 获取数据库配置
	config, err := s.GetConfig(configID)
	if err != nil {
		return nil, errors.Wrap(err, "failed to get database config")
	}

	// 生成测试结果
	result := map[string]interface{}{
		"connection_info": map[string]interface{}{
			"id":       configID,
			"name":     config.Name,
			"type":     config.Type,
			"host":     config.Host,
			"port":     config.Port,
			"database": database,
			"table":    table,
		},
		"columns_count": len(columns),
		"columns":       columns,
	}

	// 提取主键
	var primaryKey string
	for _, col := range columns {
		if col.IsPrimaryKey {
			primaryKey = col.Name
			break
		}
	}

	if primaryKey != "" {
		result["primary_key"] = primaryKey
	}

	// 提取常见列类型进行分组
	columnTypes := make(map[string]int)
	for _, col := range columns {
		columnTypes[col.Type]++
	}
	result["column_types"] = columnTypes

	s.logger.WithFields(logrus.Fields{
		"config_id":     configID,
		"columns_count": len(columns),
		"column_types":  columnTypes,
	}).Info("Table columns test complete")

	return result, nil
}

// GetDatabaseCapabilities 获取数据库能力信息
func (s *DatabaseService) GetDatabaseCapabilities(configID string) (map[string]bool, error) {
	driver, err := s.manager.GetConnection(configID)
	if err != nil {
		return nil, err
	}

	capabilities := driver.GetCapabilities()
	return map[string]bool{
		"supportsTransactions":      capabilities.SupportsTransactions(),
		"supportsSchemas":           capabilities.SupportsSchemas(),
		"supportsViews":             capabilities.SupportsViews(),
		"supportsStoredProcedures":  capabilities.SupportsStoredProcedures(),
		"supportsFunctions":         capabilities.SupportsFunctions(),
		"supportsTriggers":          capabilities.SupportsTriggers(),
		"supportsCTEs":              capabilities.SupportsCTEs(),
		"supportsWindowFunctions":   capabilities.SupportsWindowFunctions(),
		"supportsJSONType":          capabilities.SupportsJSONType(),
		"supportsArrayType":         capabilities.SupportsArrayType(),
		"supportsFullTextSearch":    capabilities.SupportsFullTextSearch(),
		"supportsPartitioning":      capabilities.SupportsPartitioning(),
		"supportsForeignKeys":       capabilities.SupportsForeignKeys(),
		"supportsCheckConstraints":  capabilities.SupportsCheckConstraints(),
		"supportsPartialIndexes":    capabilities.SupportsPartialIndexes(),
		"supportsExpressionIndexes": capabilities.SupportsExpressionIndexes(),
	}, nil
}

// CreateQueryBuilder 创建查询构建器
func (s *DatabaseService) CreateQueryBuilder(configID string) (database.SQLQueryBuilder, error) {
	// 注意：新的API不再需要传递DatabaseType参数
	return database.NewSQLQueryBuilder(), nil
}

// ExecuteBuiltQuery 执行通过查询构建器构建的查询
// 使用DTO替换接口参数
func (s *DatabaseService) ExecuteBuiltQuery(configID string, builderDTO *models.QueryBuilderDTO) (*models.QueryResult, error) {
	// 从DTO构建查询构建器
	builder := database.BuildQueryFromDTO(builderDTO)

	// 构建SQL并执行
	query, args := builder.Build()
	return s.ExecuteQuery(configID, query, args...)
}

// BatchInsert 批量插入数据
func (s *DatabaseService) BatchInsert(configID, table string, records []map[string]interface{}) error {
	if len(records) == 0 {
		return nil
	}

	// 使用新的API
	builder := database.NewInsertBuilder(table)

	// 提取所有列名
	if len(records) > 0 {
		firstRecord := records[0]
		columns := make([]string, 0, len(firstRecord))
		for col := range firstRecord {
			columns = append(columns, col)
		}

		// 设置列名
		builder.Columns(columns...)

		// 添加数据行
		for _, record := range records {
			values := make([]interface{}, 0, len(columns))
			for _, col := range columns {
				values = append(values, record[col])
			}
			builder.Values(values...)
		}
	}

	// 构建SQL并执行
	query, args := builder.Build()
	_, err := s.ExecuteNonQuery(configID, query, args...)
	return err
}

// ParseImportFile 解析导入文件
func (s *DatabaseService) ParseImportFile(fileData []byte, fileType string, config map[string]interface{}) (map[string]interface{}, error) {
	s.logger.WithFields(logrus.Fields{
		"fileType":  fileType,
		"fileSize":  len(fileData),
		"hasHeader": config["hasHeader"],
		"encoding":  config["encoding"],
		"delimiter": config["delimiter"],
	}).Info("Parsing import file")

	hasHeader, _ := config["hasHeader"].(bool)
	encoding, _ := config["encoding"].(string)
	delimiter, _ := config["delimiter"].(string)

	// TODO: 处理不同的文件编码 (encoding)
	_ = encoding // 暂时标记为已使用，避免linter错误

	var columns []string
	var preview []map[string]interface{}
	var totalRows int

	switch fileType {
	case "csv":
		// 解析CSV文件
		reader := csv.NewReader(strings.NewReader(string(fileData)))
		if delimiter != "" && len(delimiter) > 0 {
			reader.Comma = rune(delimiter[0])
		}

		// 读取所有记录
		records, err := reader.ReadAll()
		if err != nil {
			return nil, errors.Wrap(err, "failed to parse CSV file")
		}

		if len(records) == 0 {
			return nil, errors.New("CSV file is empty")
		}

		// 处理标题行
		if hasHeader && len(records) > 0 {
			columns = records[0]
			records = records[1:]
		} else if len(records) > 0 {
			// 生成默认列名
			for i := range records[0] {
				columns = append(columns, fmt.Sprintf("column%d", i+1))
			}
		}

		// 获取预览数据（最多10行）
		previewCount := 10
		if len(records) < previewCount {
			previewCount = len(records)
		}

		for i := 0; i < previewCount; i++ {
			row := make(map[string]interface{})
			for j, col := range columns {
				if j < len(records[i]) {
					row[col] = records[i][j]
				} else {
					row[col] = ""
				}
			}
			preview = append(preview, row)
		}

		totalRows = len(records)

	case "json":
		// 解析JSON文件
		var jsonData []map[string]interface{}
		if err := json.Unmarshal(fileData, &jsonData); err != nil {
			// 尝试解析为单个对象
			var singleObject map[string]interface{}
			if err2 := json.Unmarshal(fileData, &singleObject); err2 != nil {
				return nil, errors.Wrap(err, "failed to parse JSON file")
			}
			jsonData = []map[string]interface{}{singleObject}
		}

		if len(jsonData) == 0 {
			return nil, errors.New("JSON file is empty")
		}

		// 提取所有可能的列名
		columnMap := make(map[string]bool)
		for _, obj := range jsonData {
			for key := range obj {
				columnMap[key] = true
			}
		}

		for col := range columnMap {
			columns = append(columns, col)
		}

		// 排序列名以保持一致性
		sort.Strings(columns)

		// 获取预览数据
		previewCount := 10
		if len(jsonData) < previewCount {
			previewCount = len(jsonData)
		}

		for i := 0; i < previewCount; i++ {
			row := make(map[string]interface{})
			for _, col := range columns {
				if val, ok := jsonData[i][col]; ok {
					row[col] = val
				} else {
					row[col] = nil
				}
			}
			preview = append(preview, row)
		}

		totalRows = len(jsonData)

	case "excel":
		// 解析Excel文件
		f, err := excelize.OpenReader(strings.NewReader(string(fileData)))
		if err != nil {
			return nil, errors.Wrap(err, "failed to open Excel file")
		}
		defer f.Close()

		// 获取第一个工作表
		sheetName := f.GetSheetName(0)
		if sheetName == "" {
			sheets := f.GetSheetList()
			if len(sheets) == 0 {
				return nil, errors.New("Excel file has no sheets")
			}
			sheetName = sheets[0]
		}

		// 获取所有行
		rows, err := f.GetRows(sheetName)
		if err != nil {
			return nil, errors.Wrap(err, "failed to read Excel rows")
		}

		if len(rows) == 0 {
			return nil, errors.New("Excel sheet is empty")
		}

		// 处理标题行
		if hasHeader && len(rows) > 0 {
			columns = rows[0]
			rows = rows[1:]
		} else if len(rows) > 0 {
			// 生成默认列名
			for i := range rows[0] {
				columns = append(columns, fmt.Sprintf("column%d", i+1))
			}
		}

		// 获取预览数据（最多10行）
		previewCount := 10
		if len(rows) < previewCount {
			previewCount = len(rows)
		}

		for i := 0; i < previewCount; i++ {
			row := make(map[string]interface{})
			for j, col := range columns {
				if j < len(rows[i]) {
					row[col] = rows[i][j]
				} else {
					row[col] = ""
				}
			}
			preview = append(preview, row)
		}

		totalRows = len(rows)

	default:
		return nil, errors.New("unsupported file type: " + fileType)
	}

	return map[string]interface{}{
		"success":   true,
		"columns":   columns,
		"preview":   preview,
		"totalRows": totalRows,
	}, nil
}

// ExecuteImport 执行数据导入
func (s *DatabaseService) ExecuteImport(params map[string]interface{}) (map[string]interface{}, error) {
	startTime := time.Now() // 记录开始时间

	s.logger.WithFields(logrus.Fields{
		"connectionId": params["connectionId"],
		"database":     params["database"],
		"table":        params["table"],
		"mode":         params["mode"],
		"batchSize":    params["batchSize"],
	}).Info("Executing data import")

	// 提取参数
	connectionId, _ := params["connectionId"].(string)
	database, _ := params["database"].(string)
	table, _ := params["table"].(string)
	mode, _ := params["mode"].(string)
	batchSize, _ := params["batchSize"].(float64) // JSON解析的数字是float64
	if batchSize <= 0 {
		batchSize = 1000
	}

	// 处理文件数据 - 从前端传来的是数组
	var fileData []byte
	if fileDataArray, ok := params["fileData"].([]interface{}); ok {
		// 转换接口数组为字节数组
		fileData = make([]byte, len(fileDataArray))
		for i, v := range fileDataArray {
			if b, ok := v.(float64); ok {
				fileData[i] = byte(b)
			}
		}
	} else if fileDataBytes, ok := params["fileData"].([]byte); ok {
		fileData = fileDataBytes
	}

	fileType, _ := params["fileType"].(string)
	mapping, _ := params["mapping"].(map[string]interface{})
	parseConfig, _ := params["config"].(map[string]interface{})

	// 解析文件
	parseResult, err := s.ParseImportFile(fileData, fileType, parseConfig)
	if err != nil {
		return nil, errors.Wrap(err, "failed to parse import file")
	}

	// 获取数据
	var dataRows []map[string]interface{}
	hasHeader, _ := parseConfig["hasHeader"].(bool)
	delimiter, _ := parseConfig["delimiter"].(string)

	switch fileType {
	case "csv":
		// 重新解析CSV以获取所有数据
		reader := csv.NewReader(strings.NewReader(string(fileData)))
		if delimiter != "" && len(delimiter) > 0 {
			reader.Comma = rune(delimiter[0])
		}

		records, err := reader.ReadAll()
		if err != nil {
			return nil, errors.Wrap(err, "failed to parse CSV file")
		}

		// 获取列名
		columns := parseResult["columns"].([]string)
		startRow := 0
		if hasHeader {
			startRow = 1
		}

		// 转换为map格式
		for i := startRow; i < len(records); i++ {
			row := make(map[string]interface{})
			for j, col := range columns {
				if j < len(records[i]) {
					row[col] = records[i][j]
				}
			}
			dataRows = append(dataRows, row)
		}

	case "json":
		// 重新解析JSON
		var jsonData []map[string]interface{}
		if err := json.Unmarshal(fileData, &jsonData); err != nil {
			var singleObject map[string]interface{}
			if err2 := json.Unmarshal(fileData, &singleObject); err2 != nil {
				return nil, errors.Wrap(err, "failed to parse JSON file")
			}
			jsonData = []map[string]interface{}{singleObject}
		}
		dataRows = jsonData

	case "excel":
		// 重新解析Excel以获取所有数据
		f, err := excelize.OpenReader(strings.NewReader(string(fileData)))
		if err != nil {
			return nil, errors.Wrap(err, "failed to open Excel file")
		}
		defer f.Close()

		// 获取第一个工作表
		sheetName := f.GetSheetName(0)
		if sheetName == "" {
			sheets := f.GetSheetList()
			if len(sheets) == 0 {
				return nil, errors.New("Excel file has no sheets")
			}
			sheetName = sheets[0]
		}

		// 获取所有行
		rows, err := f.GetRows(sheetName)
		if err != nil {
			return nil, errors.Wrap(err, "failed to read Excel rows")
		}

		// 获取列名
		columns := parseResult["columns"].([]string)
		startRow := 0
		if hasHeader {
			startRow = 1
		}

		// 转换为map格式
		for i := startRow; i < len(rows); i++ {
			row := make(map[string]interface{})
			for j, col := range columns {
				if j < len(rows[i]) {
					row[col] = rows[i][j]
				} else {
					row[col] = ""
				}
			}
			dataRows = append(dataRows, row)
		}

	default:
		return nil, errors.New("unsupported file type: " + fileType)
	}

	// 如果是替换模式，先清空表
	if mode == "replace" {
		s.logger.WithFields(logrus.Fields{
			"database": database,
			"table":    table,
		}).Info("Truncating table before import")

		_, err := s.ExecuteNonQuery(connectionId, fmt.Sprintf("TRUNCATE TABLE `%s`.`%s`", database, table))
		if err != nil {
			// 如果TRUNCATE失败，尝试DELETE
			_, err = s.ExecuteNonQuery(connectionId, fmt.Sprintf("DELETE FROM `%s`.`%s`", database, table))
			if err != nil {
				return nil, errors.Wrap(err, "failed to clear table")
			}
		}
	}

	// 执行批量导入
	totalRows := len(dataRows)
	successCount := 0
	failedCount := 0
	importErrors := []map[string]interface{}{} // 重命名为 importErrors 避免与包冲突

	// 获取目标表的列信息
	tableColumns, err := s.GetTableColumns(connectionId, database, table)
	if err != nil {
		return nil, errors.Wrap(err, "failed to get table columns")
	}
	_ = tableColumns // TODO: 未来可以用于验证数据类型

	// 创建列名映射
	columnMapping := make(map[string]string)
	for source, target := range mapping {
		if targetStr, ok := target.(string); ok && targetStr != "" {
			columnMapping[source] = targetStr
		}
	}

	// 批量处理数据
	for i := 0; i < totalRows; i += int(batchSize) {
		end := i + int(batchSize)
		if end > totalRows {
			end = totalRows
		}

		batch := dataRows[i:end]

		// 构建批量插入SQL
		if len(columnMapping) > 0 {
			// 先将映射转换为有序的列表
			var sourceColumns []string
			var targetColumns []string
			for source, target := range columnMapping {
				sourceColumns = append(sourceColumns, source)
				targetColumns = append(targetColumns, target)
			}

			// 构建列名列表
			var columns []string
			var placeholders []string
			for _, targetCol := range targetColumns {
				columns = append(columns, fmt.Sprintf("`%s`", targetCol))
				placeholders = append(placeholders, "?")
			}

			// 构建INSERT语句
			sql := fmt.Sprintf("INSERT INTO `%s`.`%s` (%s) VALUES ",
				database, table, strings.Join(columns, ", "))

			// 构建VALUES部分
			var valueParts []string
			var args []interface{}
			for _, row := range batch {
				valuePart := "(" + strings.Join(placeholders, ", ") + ")"
				valueParts = append(valueParts, valuePart)

				// 按照相同的顺序添加参数
				for idx, sourceCol := range sourceColumns {
					targetCol := targetColumns[idx]

					// 查找对应的目标列信息，用于类型转换
					var targetColInfo *models.ColumnInfo
					for _, col := range tableColumns {
						if col.Name == targetCol {
							targetColInfo = &col
							break
						}
					}

					if val, ok := row[sourceCol]; ok {
						// 如果有目标列信息，进行类型转换
						if targetColInfo != nil {
							convertedValue, err := convertValueToTargetType(val, *targetColInfo)
							if err != nil {
								s.logger.WithFields(logrus.Fields{
									"source": sourceCol,
									"target": targetCol,
									"value":  val,
									"error":  err.Error(),
								}).Debug("Value conversion error")

								// 使用零值作为替代
								args = append(args, getZeroValueForType(*targetColInfo))
							} else {
								args = append(args, convertedValue)
							}
						} else {
							args = append(args, val)
						}
					} else {
						args = append(args, nil)
					}
				}
			}

			sql += strings.Join(valueParts, ", ")

			// 执行批量插入
			_, err := s.ExecuteNonQuery(connectionId, sql, args...)
			if err != nil {
				failedCount += len(batch)
				importErrors = append(importErrors, map[string]interface{}{
					"row":     i + 1,
					"message": err.Error(),
				})
			} else {
				successCount += len(batch)
			}
		}

		// 更新进度
		s.logger.WithFields(logrus.Fields{
			"processed": end,
			"total":     totalRows,
			"success":   successCount,
			"failed":    failedCount,
		}).Debug("Import progress")
	}

	duration := time.Since(startTime).String()

	return map[string]interface{}{
		"success":  true,
		"total":    totalRows,
		"imported": successCount,
		"failed":   failedCount,
		"errors":   importErrors,
		"duration": duration,
	}, nil
}

// CreateImportWindow 创建导入窗口
// 直接创建窗口而不是通过事件系统
func (s *DatabaseService) CreateImportWindow(config map[string]interface{}) (map[string]interface{}, error) {
	s.logger.WithFields(logrus.Fields{
		"connectionId": config["connectionId"],
		"database":     config["database"],
		"table":        config["table"],
	}).Info("Creating import window directly")

	// 验证必要的参数
	connectionId, ok := config["connectionId"].(string)
	if !ok || connectionId == "" {
		return nil, errors.New("connectionId is required")
	}

	database, ok := config["database"].(string)
	if !ok || database == "" {
		return nil, errors.New("database is required")
	}

	table, ok := config["table"].(string)
	if !ok || table == "" {
		return nil, errors.New("table is required")
	}

	// 由于前端无法直接创建窗口，我们返回一个标志让前端知道应该使用路由导航
	// 在未来的 Wails3 版本中，可能会支持从服务直接创建窗口
	return map[string]interface{}{
		"success":            true,
		"useRouteNavigation": true,
		"config": map[string]interface{}{
			"connectionId":   connectionId,
			"connectionType": config["connectionType"],
			"database":       database,
			"table":          table,
			"columns":        config["columns"],
		},
	}, nil
}

// 新增: 将值转换为目标列类型
func convertValueToTargetType(value interface{}, targetCol models.ColumnInfo) (interface{}, error) {
	// 如果值为nil，直接返回
	if value == nil {
		return nil, nil
	}

	// 转为字符串用于进一步处理
	strValue := fmt.Sprintf("%v", value)
	if strValue == "" {
		return nil, nil
	}

	// 根据目标列类型进行转换
	lowerType := strings.ToLower(targetCol.Type)

	// 整数类型
	if strings.Contains(lowerType, "int") || strings.Contains(lowerType, "bit") {
		// 处理可能的布尔值
		if strValue == "true" || strValue == "yes" || strValue == "y" {
			return 1, nil
		}
		if strValue == "false" || strValue == "no" || strValue == "n" {
			return 0, nil
		}

		// 尝试转换为整数
		intVal, err := strconv.ParseInt(strValue, 10, 64)
		if err != nil {
			// 如果包含小数点，尝试先转为float然后取整
			if strings.Contains(strValue, ".") {
				floatVal, floatErr := strconv.ParseFloat(strValue, 64)
				if floatErr != nil {
					return 0, fmt.Errorf("无法转换为整数: %s", strValue)
				}
				return int64(floatVal), nil
			}
			return 0, fmt.Errorf("无法转换为整数: %s", strValue)
		}
		return intVal, nil
	}

	// 浮点数类型
	if strings.Contains(lowerType, "float") || strings.Contains(lowerType, "double") || strings.Contains(lowerType, "decimal") || strings.Contains(lowerType, "numeric") {
		// 尝试转换为浮点数
		floatVal, err := strconv.ParseFloat(strValue, 64)
		if err != nil {
			return 0.0, fmt.Errorf("无法转换为浮点数: %s", strValue)
		}
		return floatVal, nil
	}

	// 日期类型
	if strings.Contains(lowerType, "date") && !strings.Contains(lowerType, "time") {
		// 尝试解析为日期
		t, err := parseAnyDate(strValue)
		if err != nil {
			return nil, fmt.Errorf("无法解析为日期: %s", strValue)
		}
		return t.Format("2006-01-02"), nil
	}

	// 时间类型
	if strings.Contains(lowerType, "time") && !strings.Contains(lowerType, "date") {
		// 尝试解析为时间
		t, err := parseAnyDate(strValue)
		if err != nil {
			return nil, fmt.Errorf("无法解析为时间: %s", strValue)
		}
		return t.Format("15:04:05"), nil
	}

	// 日期时间类型
	if strings.Contains(lowerType, "datetime") || strings.Contains(lowerType, "timestamp") {
		// 尝试解析为日期时间
		t, err := parseAnyDate(strValue)
		if err != nil {
			return nil, fmt.Errorf("无法解析为日期时间: %s", strValue)
		}
		return t.Format("2006-01-02 15:04:05"), nil
	}

	// 字符串类型
	if (strings.Contains(lowerType, "varchar") || strings.Contains(lowerType, "char") || strings.Contains(lowerType, "text")) && targetCol.Length > 0 {
		// 截断超长字符串
		if len(strValue) > targetCol.Length {
			return strValue[:targetCol.Length], nil
		}
	}

	// 其他类型或不需要转换的情况，直接返回原值
	return value, nil
}

// 新增: 根据列类型获取零值
func getZeroValueForType(targetCol models.ColumnInfo) interface{} {
	lowerType := strings.ToLower(targetCol.Type)

	// 整数类型
	if strings.Contains(lowerType, "int") || strings.Contains(lowerType, "bit") {
		return 0
	}

	// 浮点数类型
	if strings.Contains(lowerType, "float") || strings.Contains(lowerType, "double") || strings.Contains(lowerType, "decimal") || strings.Contains(lowerType, "numeric") {
		return 0.0
	}

	// 日期类型
	if strings.Contains(lowerType, "date") && !strings.Contains(lowerType, "time") {
		return "0000-00-00"
	}

	// 时间类型
	if strings.Contains(lowerType, "time") && !strings.Contains(lowerType, "date") {
		return "00:00:00"
	}

	// 日期时间类型
	if strings.Contains(lowerType, "datetime") || strings.Contains(lowerType, "timestamp") {
		return "0000-00-00 00:00:00"
	}

	// 布尔类型
	if strings.Contains(lowerType, "bool") {
		return false
	}

	// 字符串类型和其他类型
	return ""
}

// 新增: 尝试使用多种格式解析日期
func parseAnyDate(dateStr string) (time.Time, error) {
	// 常见的日期格式
	formats := []string{
		"2006-01-02",
		"2006/01/02",
		"02/01/2006",
		"01/02/2006",
		"2006-01-02 15:04:05",
		"2006/01/02 15:04:05",
		"02/01/2006 15:04:05",
		"01/02/2006 15:04:05",
		"2006-01-02T15:04:05Z",
		"2006-01-02T15:04:05",
		time.RFC3339,
		time.RFC3339Nano,
	}

	for _, format := range formats {
		t, err := time.Parse(format, dateStr)
		if err == nil {
			return t, nil
		}
	}

	// 尝试使用time.Parse自动判断
	t, err := time.Parse(time.RFC3339, dateStr)
	if err == nil {
		return t, nil
	}

	// 如果所有格式都失败，尝试Unix时间戳
	if i, err := strconv.ParseInt(dateStr, 10, 64); err == nil {
		return time.Unix(i, 0), nil
	}

	return time.Time{}, fmt.Errorf("无法解析日期时间格式: %s", dateStr)
}

// TruncateTable 清空表
func (s *DatabaseService) TruncateTable(configId, database, table string) error {
	s.logger.WithFields(logrus.Fields{
		"config_id": configId,
		"database":  database,
		"table":     table,
	}).Info("Truncating table")

	// 参数验证
	if configId == "" {
		return errors.New("connection ID is required")
	}
	if database == "" {
		return errors.New("database name is required")
	}
	if table == "" {
		return errors.New("table name is required")
	}

	// 尝试执行TRUNCATE语句
	_, err := s.ExecuteNonQuery(configId, fmt.Sprintf("TRUNCATE TABLE `%s`.`%s`", database, table))
	if err != nil {
		// 如果TRUNCATE失败，尝试使用DELETE
		s.logger.WithError(err).Warn("TRUNCATE failed, trying DELETE")
		_, err = s.ExecuteNonQuery(configId, fmt.Sprintf("DELETE FROM `%s`.`%s`", database, table))
		if err != nil {
			s.logger.WithError(err).Error("Failed to truncate table")
			return errors.Wrap(err, "failed to truncate table")
		}
	}

	s.logger.WithFields(logrus.Fields{
		"config_id": configId,
		"database":  database,
		"table":     table,
	}).Info("Table truncated successfully")

	return nil
}

// AlterTable 修改表结构
func (s *DatabaseService) AlterTable(configID, database, table string, alterSchema *models.TableAlterSchema) error {
	s.logger.WithFields(logrus.Fields{
		"config_id": configID,
		"database":  database,
		"table":     table,
	}).Info("Altering table")

	// 获取数据库连接
	driver, err := s.manager.GetConnection(configID)
	if err != nil {
		return errors.Wrap(err, "获取数据库连接失败")
	}

	// 生成修改表的SQL语句
	sqlStatements, err := driver.GenerateAlterTableSQL(database, table, alterSchema)
	if err != nil {
		return errors.Wrap(err, "生成修改表SQL失败")
	}

	// 增加日志记录：输出生成的SQL语句
	if len(sqlStatements) > 0 {
		s.logger.WithFields(logrus.Fields{
			"sql_count": len(sqlStatements),
			"sql":       strings.Join(sqlStatements, "; "),
		}).Debug("Generated SQL statements for altering table")
	} else {
		s.logger.Warn("No SQL statements generated for altering table - no changes needed")
		return nil
	}

	// 执行SQL并确保错误被正确处理和传递
	result, err := s.ExecuteBatchSQL(configID, sqlStatements)
	if err != nil {
		s.logger.WithError(err).Error("执行修改表SQL失败")
		return errors.Wrap(err, "执行修改表SQL失败")
	}

	// 检查执行结果是否有错误
	if result.FailedCount > 0 {
		// 收集所有错误信息
		var errorMsgs []string
		for _, stmtResult := range result.Results {
			if !stmtResult.Success && stmtResult.Error != "" {
				errorMsgs = append(errorMsgs, stmtResult.Error)
			}
		}

		if len(errorMsgs) > 0 {
			errorMsg := "执行修改表SQL失败: " + strings.Join(errorMsgs, "; ")
			s.logger.Error(errorMsg)
			return errors.New(errorMsg)
		}
	}

	// 记录执行成功日志
	s.logger.WithFields(logrus.Fields{
		"config_id":    configID,
		"database":     database,
		"table":        table,
		"success":      result.SuccessCount,
		"execute_time": result.TotalExecuteTime,
	}).Info("Table structure altered successfully")

	// 记录表结构变更事件
	s.logger.WithFields(logrus.Fields{
		"event":        "refresh:table-structure",
		"connectionId": configID,
		"database":     database,
		"table":        table,
		"timestamp":    time.Now().UnixNano() / int64(time.Millisecond),
	}).Info("Table structure changed, frontend should refresh")

	return nil
}
